package br.com.zumbits.avd.mysql;

//classe para conectar com MySQL.
import br.com.zumbits.avd.storage.StorageManager;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.LinkedList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Query {

    //private static String consulta;
    private Connection connection;
    private ResultSet resultSet;
    private Statement statement;

    /**
     * 
     * @param Db
     */
    public Query(Connection connection) {
        try {
            this.connection = connection;
            this.statement = connection.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Mysql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void Log(String str) {
        FileWriter writer = null;
        try {
            File file = new File("/home/pet/madnexus/avd/storege/log.txt");

            writer = new FileWriter(file);

            writer.write(str);
        } catch (IOException ex) {
            Logger.getLogger(StorageManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                writer.close();
            } catch (IOException ex) {
                Logger.getLogger(StorageManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public Connection getConnection() {
        return connection;
    }

    /**
     * 
     * @param sql
     * @return
     */
    public boolean executeQuery(String sql) {
        try {
            resultSet = statement.executeQuery(sql);
            return true;
        } catch (SQLException ex) {
            Log("Erro ao executar consulta! - " + sql + "\nMotivo: " + ex.getMessage());
        }

        return false;
    }

    public String[] executeQueryFilterName(String sql, String filter) {
        LinkedList list = new LinkedList();
        String novaList[] = null;

        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                if (filter.regionMatches(true, 0, resultSet.getString("nome"),
                        0, filter.length())) {
                    list.add(resultSet.getString("nome"));
                }
            }
            int i = list.size();
            novaList = new String[i];
            while (--i > -1) {
                novaList[i] = list.removeLast().toString();
            }
        } catch (SQLException ex) {
            System.out.println("Erro ao executar consulta! - " + sql);
            Logger.getLogger(Mysql.class.getName()).log(Level.SEVERE, null, ex);
        }

        list = null;

        return novaList;
    }

    /**
     * 
     * @param sql
     */
    public boolean execute(String sql) {
        try {
            statement.execute(sql);
            return true;
        } catch (SQLException ex) {
            System.out.println("Erro ao executar consulta! - " + sql);
            Logger.getLogger(Mysql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    /**
     * 
     * @param ID
     * @param tabela
     * @return
     */
    public int maxID(String column, String tabela) {
        String max = "MAX(" + column + ")";
        String sql = "SELECT " + max + " FROM " + tabela;
        int id = 0;

        try {
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                try {
                    int maxValor = 0;
                    if (resultSet.getString(max) == null) {
                        maxValor = 0;
                    } else {
                        maxValor = Integer.parseInt(resultSet.getString(max));
                    }

                    id = maxValor;
                } catch (NumberFormatException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException ex) {
            System.out.println("Erro ao executar consulta! - " + sql);
            Logger.getLogger(Mysql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return id;
    }

    /**
     * 
     * @return
     */
    public boolean next() throws SQLException {
        if (resultSet.next()) {
            return true;
        }

        return false;
    }

    /**
     * 
     * @return
     */
    public boolean prev() throws SQLException {
        if (resultSet.previous()) {
            return true;
        }

        return false;
    }

    /**
     * 
     * @return
     */
    public boolean first() throws SQLException {
        if (resultSet.first()) {
            return true;
        }

        return false;
    }

    /**
     * 
     * @return
     */
    public boolean last() throws SQLException {
        if (resultSet.last()) {
            return true;
        }

        return false;
    }

    public String getString(Object columnLabel) throws SQLException {
        if (columnLabel instanceof String) {
            return resultSet.getString((String) columnLabel);
        } else {
            return resultSet.getString((Integer) columnLabel);
        }
    }

    public int getInt(Object columnLabel) throws SQLException {
        if (columnLabel instanceof String) {
            return resultSet.getInt((String) columnLabel);
        } else {
            return resultSet.getInt((Integer) columnLabel);
        }
    }
}
